package cn.edu.zjgsu.ito.service;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import   org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.ss.usermodel.Cell;






import cn.edu.zjgsu.ito.model.RosterModel;
import cn.edu.zjgsu.ito.model.StudentModel;
import cn.edu.zjgsu.ito.model.TeacherModel;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class TeaStuService {
	public List<Record> allStu(String uuid) {
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		try {
			List<Record> list=Db.find(RosterModel.dao.selectSql,uuid);
			for(int i=0;i<list.size();i++){
				String []b= new String[100];
				Date []date=new Date[100];
				Date dates=new Date();
				String Dates="";
				List<Record> lists = Db.find("select * from newpassportno where passportNo=?",list.get(i).getStr("passportNo"));
				if(lists.size()>1){
					for(int j=0;j<lists.size();j++){
										b[j] = lists.get(j).getStr("time");
										date[j] = formatter.parse(b[j]);
									}
									System.out.println(lists.size());
									for(int a=0;a<lists.size()-1;a++){
										 dates = date[a];
										int c = (date[a+1]).compareTo(dates);
										if(c>0){
											dates = date[a+1];
										}
									}
									Dates = formatter.format(dates);
								
									Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
									list.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
				else{
					Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(i).getStr("passportNo"));
					list.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				}		
				
			}
			System.out.println("hehhehe");
			System.out.println(list);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}
	public Record TTT(String uuid){
		 try {
				Record record=Db.findFirst("select * from teacher_timetable where uuid=?",uuid);

				return record;
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		 
	 }
	public Record TeaTT(String uuid) {
		try {
			Record list=Db.findFirst(TeacherModel.dao.selectTtt2Sql,uuid);

			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}
	
	public Record StuInf(String id) {
		try {
			Record list=Db.findFirst(StudentModel.dao.selectStuInfoSql,id);

			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	
	/**
	 * 创建excel
	 * 导出学生
	 */

	 @SuppressWarnings("deprecation")
	public void exportStuExcel(Record record,

			 List<Record> dataset, OutputStream out,String uuid) {
		 HSSFWorkbook workbook = new HSSFWorkbook(); 
		 try{
			      
	         HSSFSheet sheet = workbook.createSheet("new   sheet");      
	         HSSFCellStyle style = workbook.createCellStyle(); // 样式对象 
	         HSSFCellStyle style1 = workbook.createCellStyle(); // 样式对象
             
             HSSFPrintSetup ps = sheet.getPrintSetup();//打印设置
              ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)  
              ps.setVResolution((short)600);  
              ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型  
             
              
	         //字体  加粗
	         HSSFFont font1 = workbook.createFont();

		  	 font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		  	 font1.setFontHeightInPoints((short) 12);//设置字体大小
		  	 font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		  	 
		  	//字体  加粗
	         HSSFFont font = workbook.createFont();

		  	 font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		  	 font.setFontHeightInPoints((short) 9);//设置字体大小
	         
		  	    // 设置这些样式

		      style.setFillForegroundColor(HSSFColor.WHITE.index);

		      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

		      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

		      style.setBorderRight(HSSFCellStyle.BORDER_THIN);

		      style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	  
		      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
	       
		      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		      		             
		      style.setFont(font1);
              
              
		      
		      
		      style1.setFillForegroundColor(HSSFColor.WHITE.index);

		      style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		      style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

		      style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

		      style1.setBorderRight(HSSFCellStyle.BORDER_THIN);

		      style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

		      style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	  
		      style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
	       
		      style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		      
		      style1.setFont(font);
              
              
              style1.setWrapText(true);
              style.setWrapText(true);
                       	       
		       int width=4000;
		       int height=500;
		       int height1=350;
		       
		       HSSFRow row = sheet.createRow((short) 0);
//		       HSSFRow row1 = sheet.createRow((short) 1);
		       HSSFRow row3 = sheet.createRow((short) 1);
//		       sheet.addMergedRegion(new Region(0, (short) 9, 0, (short) 10));// 第一个参数：开始行  第二个参数：开始列  第三个参数：结束行 第四个参数：结束列    
//		       sheet.addMergedRegion(new Region(0, (short) 2, 0, (short) 3));
//		       sheet.addMergedRegion(new Region(0, (short) 9, 0, (short) 10));            
//		       sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 4));
//		       sheet.addMergedRegion(new CellRangeAddress(0, 11, 0,12));  
//		       sheet.addMergedRegion(new CellRangeAddress(0, 9, 0,10));  
//		       sheet.addMergedRegion(new Region(0, (short) 9, 0, (short) 10)); 
//		       sheet.addMergedRegion(new Region(0, (short) 11, 0, (short) 12)); 
		       sheet.addMergedRegion(new CellRangeAddress(0,0,9,10));
		       sheet.addMergedRegion(new CellRangeAddress(0,0,11,13));
	         
	         
//	         sheet.createRow(0).setHeight((short) height);//第一行
//	         sheet.createRow(1).setHeight((short) height1);//第二行
//	         sheet.createRow(2).setHeight((short) height1);//第三行
//	         sheet.createRow(3).setHeight((short) height1);//第四行
	   
	         //列宽
	         sheet.setColumnWidth(0, 1000);
             sheet.setColumnWidth(1, (short) (7.86*256));
             sheet.setColumnWidth(2, (short) (16.86*256));
             sheet.setColumnWidth(3, (short) (10.86*256));
             sheet.setColumnWidth(4, (short) (13.86*256));
             sheet.setColumnWidth(5, (short) (13.86*256));
             sheet.setColumnWidth(6, (short) (19.86*256));
             sheet.setColumnWidth(7, (short) (11.86*256));
             sheet.setColumnWidth(8, (short) (10.86*256));
             sheet.setColumnWidth(9, (short) (3.86*256));
             sheet.setColumnWidth(10, (short) (3.86*256));
             sheet.setColumnWidth(11, (short) (3.86*256));
             sheet.setColumnWidth(12, (short) (3.86*256));
             sheet.setColumnWidth(13, (short) (3.86*256));
	         
	         Iterator<Record> it = dataset.iterator();
             System.out.println("进来了，看看传过来的数据："+record);

	         //第一行
	         HSSFCell ce = row.createCell((short) 1);          
	         ce.setCellValue("课程名");      
	         ce.setCellStyle(style);
	         HSSFCell ce0 = row.createCell((short) 2);          
	         ce0.setCellValue(record.getStr("courseName"));      
	         ce0.setCellStyle(style);
	         
	         HSSFCell ce1 = row.createCell((short) 3);           
	         ce1.setCellValue("授课教师");   
	         ce1.setCellStyle(style);
	         HSSFCell ce001 = row.createCell((short) 4);
	         Record TName=Db.findFirst("select tName from teacher_info where tNo=?",record.getStr("tNo"));
	         ce001.setCellValue(TName.getStr("tName"));   
	         ce001.setCellStyle(style); 
	                     
	         HSSFCell ce2 = row.createCell((short) 5);           
	         ce2.setCellValue("起始周");    
	         ce2.setCellStyle(style);
	         HSSFCell ce002 = row.createCell((short) 6);           
	         ce002.setCellValue(record.getStr("startWeek")+"-"+record.getStr("endWeek"));    
	         ce002.setCellStyle(style);
	                     
	         HSSFCell ce3 = row.createCell((short) 7);           
	         ce3.setCellValue("上课时间");    
	         ce3.setCellStyle(style);
	         HSSFCell ce03 = row.createCell((short) 8);           
	         ce03.setCellValue(record.getStr("startLesson")+"-"+record.getStr("endLesson"));    
	         ce03.setCellStyle(style);
             
	         List<Record> temp = Db.find("select * from teacher_timetable where uuid=?",uuid);
	         String room = "";
	         for(int i = 0;i < temp.size();i++){
	        	 room += temp.get(i).getStr("classroomNo");
	         }
	         HSSFCell ce9 = row.createCell(9);           
	         ce9.setCellValue("教室");    
	         ce9.setCellStyle(style);
	         HSSFCell ce10 = row.createCell( 11);           
	         ce10.setCellValue(room);    
	         ce10.setCellStyle(style);
	         
	         HSSFCell ce11 = row.createCell(10);    
	         HSSFCell ce12 = row.createCell(12); 
	         HSSFCell ce13 = row.createCell(13); 
	         ce11.setCellStyle(style);
	         ce12.setCellStyle(style);
	         ce13.setCellStyle(style);
	         
	         
	         for(int i=9;i<14;i++){
                HSSFCell ce00 = row3.createCell((short) i);
                ce00.setCellStyle(style);
	         }
	         
//	         //第二行
//	         
//	         HSSFCell ce4 = row1.createCell((short) 1);
//	         ce4.setCellValue("专业");
//	         ce4.setCellStyle(style);
//	         HSSFCell ce5 = row1.createCell((short) 5);
//	         ce5.setCellValue("入学年份");
//	         ce5.setCellStyle(style);
//
//	         
//	         HSSFCell ce6 = row1.createCell((short) 7);
//	         ce6.setCellValue("入学季度");
//	         ce6.setCellStyle(style);
//	         
//	         for(int i=1;i<11;i++){
//	         	if(i!=1&&i!=5&&i!=7){
//	         		HSSFCell ce01 = row1.createCell((short) i);
//	         		ce01.setCellStyle(style);
//	         	}
//	         }

	         //第四行
	         String[] Headers = { "序号", "学号", "护照号", "姓", "名", "专业", "入学年份", "入学季度"};
	         for (short i = 0; i <Headers.length; i++) {

		         HSSFCell cell = row3.createCell(i+1);

		         cell.setCellStyle(style);

		         HSSFRichTextString text = new HSSFRichTextString(Headers[i]);

		         cell.setCellValue(text);

		      }
             for(int kk=9;kk<14;kk++){
                    HSSFCell ce00 = row3.createCell((short) kk);
                    ce00.setCellStyle(style);
                 }
		      
	         
	         try{
	        	 int index = 1,order=1;
	   	      
	   	      while (it.hasNext()) {
	   	    	  index++;

	   	          row = sheet.createRow(index);

	   	          Record t = (Record) it.next();
	   	          
	   	          String[] Context = { String.valueOf(order++),t.getStr("sNo"), t.getStr("passportNo"), t.getStr("lastName"), t.getStr("firstName"),t.getStr("majorName"), t.getStr("eduStartDate"),t.getStr("eduSeason")};
	   	          for (short i = 0; i < Headers.length; i++) {

	   	 	         HSSFCell cell = row.createCell(i+1);

	   	 	         cell.setCellStyle(style1);

	   	 	         HSSFRichTextString text = new HSSFRichTextString(Context[i]);

	   	 	         cell.setCellValue(text);

	   	 	      }
                  for(int kk=9;kk<14;kk++){
                    HSSFCell ce00 = row.createCell((short) kk);
                    ce00.setCellStyle(style);
                 }
	 	    	  
	   	      }
	        	 
	         }catch(Exception e){
	        	 e.printStackTrace();
	         }
		 }catch(Exception e){
			 e.printStackTrace();
		 }

		 
    

	      try {

	         workbook.write(out);

	      } catch (IOException e) {

	         // TODO Auto-generated catch block

	         e.printStackTrace();

	      }

	 

	   }

}
